/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package actualizador;

/**
 *
 * @author TomasQuinteros
 */

import java.sql.*;

public class FirebirdConn {
  
  private String        url     = "";
  private String        usr     = "SYSDBA"; 
  private String        pswd    = ""; 
  private String        driver  = "org.firebirdsql.jdbc.FBDriver";
  private Connection    con;
  //private Connection    con2;  
  public  Statement     stmt    = null;
  public  ResultSet     rs      = null; 
  public  PreparedStatement stmtPrepare;
  public  PreparedStatement stmtPrepareLog;
      
 public Connection getConnection(){
      return con;
  }
  
  public void SetPswd(String ps) {
          this.pswd = ps;
    }
     
  public boolean Conectar() {
  boolean resultado = true;
  try {

         Class.forName(this.driver);
         AddLog("Conectando a base Firebird..."+this.getUrl());
         this.con = DriverManager.getConnection( this.getUrl(), this.getUsr(),this.pswd);
         //this.con.setAutoCommit(false);
         AddLog("Conexión exitosa a base Firebird..."+this.getUrl());
         resultado = true;
      }

      catch (SQLException se) {
         try {
            AddLog("Conexion a  Firebird  fallo, " +this.getUrl()+"-"+this.getUsr()+"-"+this.pswd);
            AddLog("Conexion a Firebird  fallo, " +se.toString());
            resultado = false;
         }
         catch (Exception e) {
            e.printStackTrace();
            resultado = false;
         }
      }
      catch (Exception e) {
         e.printStackTrace();
         resultado = false;
      }
      // Close the JDBC objects.

  return resultado;
      
   }
    
 public void Cerrarconexion()
  {
        if (this.con != null) 
             try { this.con.close(); } 
             catch(Exception e) {}
        //if (this.con2 != null) 
             //try { this.con2.close(); } 
             //catch(Exception e) {}      
  }
    
 public void InsertarTabla(ResultSet rsi,String qry, int n_param) {
   try {       
       while (rsi.next()) {
                this.stmtPrepare = this.con.prepareStatement(qry);            
                //AddLog("EJECUTANDO QRY:"+qry);
                AddLog("!!!TRANSFIRIENDO DATOS..........!!!");
                AddLog("Insertando datos en tabla..."); 
             
                for (int i = 1; i <= n_param ; i++) {
                    this.stmtPrepare.setObject(i,rsi.getObject(i));    
                } 
                
                this.stmtPrepare.executeUpdate();
                this.stmtPrepare.close();
                AddLog("Replicacion tabla ok...");             
        }
   }
    catch (SQLException se) {
            try {
                AddLog("error en InsertarTabla, " + se.toString());
                this.stmtPrepare.close();
            } catch (SQLException ex) { }
    }
                 
   }
     
 public void AddLog(String valor) {
      
      System.out.println(valor);
 /*  try {       
                this.stmtPrepareLog = this.con.prepareStatement("INSERT INTO CR_LOG(DESCRIPCION)VALUES(?)");                        
                this.stmtPrepareLog.setString(1, valor);          
                this.stmtPrepareLog.executeUpdate();
                this.stmtPrepareLog.close();                         
        }
   catch (SQLException se) {
                System.out.println("error en AddLog, " +se.toString());
                 
    }*/
                 
}
            
  public ResultSet LeerTablaParaMigrar(String nombreTabla) {
   try {
            this.stmt  = this.con.createStatement();
            String SQL = "SELECT * FROM "+nombreTabla+" WHERE SITUACION='P'";
            AddLog("leyendo tabla "+nombreTabla+"...");
            this.rs = this.stmt.executeQuery(SQL);
   }
    catch (SQLException se) {
            AddLog("error en lecura de tabla "+nombreTabla+", " +se.toString());
                 
    }
       return this.rs;
   }

  public ResultSet TablasNormalizar() {
   try {
            this.stmt  = this.con.createStatement();
            String SQL = "SELECT * FROM CR_TABLAS_NORMALIZAR";
            AddLog("leyendo configuracion para normalizar datos...");
            this.rs = this.stmt.executeQuery(SQL);
   }
    catch (SQLException se) {
            AddLog("error en Normalizar, " +se.toString());
                 
    }
       return this.rs;
   } 

 public int GuiasNuevas() {
   int guia = 0;  
   try {
            this.stmt  = this.con.createStatement();
            String SQL = "SELECT COUNT(*) AS CUANTOS FROM IMP_AWB WHERE NORMALIZADO='N'";
            AddLog("leyendo si hay guias nuevas...");
            this.rs = this.stmt.executeQuery(SQL);
            this.rs.next();
            guia =  this.rs.getInt("CUANTOS");
   }
    catch (SQLException se) {
            AddLog("error leyendo imp_awb, " +se.toString());
                 
    }
       return guia;
   } 

  public ResultSet ListadoGuiasNuevas() {
   int guia = 0;  
   try {
            this.stmt  = this.con.createStatement();
            String SQL = "SELECT AWB FROM IMP_AWB WHERE NORMALIZADO='N'";
            AddLog("leyendo guias nuevas...");
            this.rs = this.stmt.executeQuery(SQL);
   }
    catch (SQLException se) {
            AddLog("error leyendo imp_awb, " +se.toString());
                 
    }
       return this.rs;
   }
  
 public void CerrarStatement(){
        try {
            this.stmt.close();
        } catch (SQLException ex) {
            
        }
 }        
  
  public ResultSet LeerSqlUpdate() {
   try {    
            this.stmt  = this.con.createStatement();
            String SQL = "SELECT * FROM CR_SQL_UPDATE_SQLSERVER";
            AddLog("leyendo registros para actualizar sqlserver...");
            this.rs = this.stmt.executeQuery(SQL);           
   }
    catch (SQLException se) {
            AddLog("error en LeerSqlUpdate, " +se.toString());
                 
    }
            return this.rs;        
   }
  
  
  public ResultSet LeerFacturasParaPasar() {
   try {    
            this.stmt  = this.con.createStatement();
            String SQL = "SELECT QRY FROM SP_CR_TRAE_FACTURAS";
            AddLog("leyendo facturas...");
            this.rs = this.stmt.executeQuery(SQL);           
   }
    catch (SQLException se) {
            AddLog("error en LeerFacturasParaPasar, " +se.toString());
                 
    }
            return this.rs;        
   }
  
  
  public void LlenarTablasImpSigad() {
   try {    
            this.stmt  = this.con.createStatement();
            String SQL = "EXECUTE PROCEDURE SP_CR_IMP_DOCUMENTO_MASIVO";
            AddLog("CARGANDO TABLAS IMP EN SIGAD...");
            this.rs = this.stmt.executeQuery(SQL);           
   }
    catch (SQLException se) {
            AddLog("error en LlenarTablasImpSigad, " +se.toString());
                 
    }
                    
   }
  
  
  public ResultSet BorrarSqlUpdate() {
   try {    
            this.stmt  = this.con.createStatement();
            String SQL = "DELETE FROM CR_SQL_UPDATE_SQLSERVER";
            AddLog("borrando los update...");
            this.stmt.executeUpdate(SQL);           
   }
    catch (SQLException se) {
            AddLog("error en BorrarSqlUpdate, " +se.toString());
                 
    }
            return this.rs;        
   }  
  
  
  public ResultSet EjecutarSql(String qry) {
   try {    
            this.stmt  = this.con.createStatement();
            String SQL = qry;
            AddLog("ejecutando..."+qry);
            this.stmt.executeUpdate(SQL);           
   }
    catch (SQLException se) {
            AddLog("error en EjecutarSql, " +se.toString());
                 
    }
            return this.rs;        
   }
  
   public ResultSet UpdateDatos(String qry) throws SQLException {
   try {    
            this.stmt  = this.con.createStatement();
            String SQL = qry;
            AddLog("actualizando Firebird..."+qry);
            AddLog("Estados de Registros Actualizados!");
            this.stmt.executeUpdate(SQL);   
            return this.rs;
   }
    catch (SQLException se) {
            AddLog("error en actualización de registros, " +se.toString());
                 throw se;
    }
        
   }
  
  
 public void CerrarLog() {
  
  
         
}

    /**
     * @return the usr
     */
    public String getUsr() {
        return usr;
    }

    /**
     * @param usr the usr to set
     */
    public void setUsr(String usr) {
        this.usr = usr;
    }

    /**
     * @return the url
     */
    public String getUrl() {
        return url;
    }

    /**
     * @param url the url to set
     */
    public void setUrl(String url) {
        this.url = url;
    } 
}
